package com.dy.yunying.biz.dao.clickhouse3399.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.collection.ListUtil;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.dy.yunying.api.constant.Constant;
import com.dy.yunying.api.vo.PlanAttrAnalyseSearchVo;
import com.pig4cloud.pig.api.vo.PlanBaseAttrVo;
import com.pig4cloud.pig.common.core.constant.enums.PlanAttrStatTypeEnum;
import com.pig4cloud.pig.common.core.exception.BusinessException;
import io.trino.jdbc.$internal.guava.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.jpedal.parser.shape.S;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
@Component
public class AdPlanAttrStatDao {

	@Resource(name = "clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;
	//高频字符串
	private String DAY = "day";

	public List<PlanBaseAttrVo> selectPlanAttrAnalyseReoport(PlanAttrAnalyseSearchVo searchVo) {
		StringBuilder sql = new StringBuilder();
		List<String> queryColumn = searchVo.getQueryColumn();
		final String groupByColumn = convertGroupByColumns(queryColumn);
		final Integer isSys = searchVo.getIsSys();
		final String userIds = searchVo.getUserIds();
		final String adAccounts = searchVo.getAdAccounts();
		final Integer os = searchVo.getOs();
		final String appchlArr = searchVo.getAppchlArr();
		final String parentchlArr = searchVo.getParentchlArr();
		final String investorArr = searchVo.getInvestorArr();
		final String deptIdArr = searchVo.getDeptIdArr();
		final String userGroupIdArr = searchVo.getUserGroupIdArr();
		final String pgidArr = searchVo.getPgidArr();
		final String adStatusArr = searchVo.getAdStatusArr();
		final String day = searchVo.getDate();
		final String gameidArr = searchVo.getGameidArr();
		final String convertArr = searchVo.getConvertArr();
		final String deepConvertArr = searchVo.getDeepConvertArr();
		final String convertDataTypeArr = searchVo.getConvertDataTypeArr();
		final String adMaterialName = searchVo.getAdMaterialName();
		// 通用筛选条件
		StringBuffer commCondSB = new StringBuffer("");

		if (os != null) {
			commCondSB.append("                     AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append("                     AND pgid IN (").append(pgidArr).append(Constant.RIGHT_SMALL_BRANCKET);
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append("                     AND gameid IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			commCondSB.append("                     AND appchl = '").append(appchlArr).append("'");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append("                     AND parentchl IN ('").append(parentchlArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append("                     AND deptId IN (").append(deptIdArr).append(Constant.RIGHT_SMALL_BRANCKET);
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			commCondSB.append("                     AND userGroupId IN (").append(userGroupIdArr).append(Constant.RIGHT_SMALL_BRANCKET);
		}
		if (StringUtils.isNotBlank(investorArr)) {
			commCondSB.append("                     AND investor IN (").append(investorArr).append(Constant.RIGHT_SMALL_BRANCKET);
		}
		if (StringUtils.isNotBlank(adStatusArr)) {
			commCondSB.append("                     AND adStatus IN ('").append(adStatusArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if (StringUtils.isNotBlank(convertArr)) {
			commCondSB.append("     and convertName IN ('").append(convertArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if ("empty".equals(deepConvertArr)) {
			commCondSB.append("     and (deepConvert = '' OR deepConvert IS NULL)");
		} else if (StringUtils.isNotBlank(deepConvertArr)) {
			commCondSB.append("     and deepConvert = '").append(deepConvertArr).append("'");
		}
		if (StringUtils.isNotBlank(convertDataTypeArr)) {
			commCondSB.append("     and convertDataType = '").append(convertDataTypeArr).append("'");
		}
		if (StringUtils.isNotBlank(adMaterialName)) {
			commCondSB.append("                     AND adMaterialName like '%").append(adMaterialName).append("%'");
		}
		String commCond = commCondSB.toString();
		log.info("commCond : [{}]", commCond);

		//处理汇总
		if (searchVo.getCycleType() == 4) {
			sql.append(" SELECT ");
			sql.append(" sum(IFNULL(cost,toDecimal32(0,3))) cost,");
			sql.append(" sum(IFNULL(rudecost,toDecimal32(0,3)))rudecost, ");
			sql.append(" sum(IFNULL(usrnamenums,0)) usrnamenums, ");
			sql.append(" sum(IFNULL(retention2,0)) retention2, ");
			sql.append(" sum(IFNULL(retention3,0)) retention3, ");
			sql.append(" sum(IFNULL(retention4,0)) retention4, ");
			sql.append(" sum(IFNULL(retention5,0)) retention5, ");
			sql.append(" sum(IFNULL(retention6,0)) retention6,\n" +
					"\tsum(IFNULL(retention7,0)) retention7,\n" +
					"\tsum(IFNULL(retention8,0)) retention8,\n" +
					"\tsum(IFNULL(paydeviceAll,0)) paydeviceAll,\n" +
					"\tCASE when sum(worth1) is null then 0 else sum(worth1) end worth1,\n" +
					"\tsum(IFNULL(paydevice1,0)) paydevice1,\n" +
					"\tCASE when sum(userfeeAll) is null then 0 else sum(userfeeAll) end userfeeAll,\n" +
					"\tsum(IFNULL(givemoneyAll,toDecimal32(0,3))) givemoneyAll ");
		} else {
			sql.append(" SELECT ");
			sql.append("     day,");
			sql.append("     id,");
			if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.ADID.V())) {
				sql.append("     adAccountId,");
				sql.append("     adAccountName,");
				sql.append("     convertName,");
				sql.append("     convertDescri,");
				sql.append("     createTime,");
			}
			if (CollectionUtils.isNotEmpty(queryColumn) && (queryColumn.contains(PlanAttrStatTypeEnum.ADID.V()) || queryColumn.contains(PlanAttrStatTypeEnum.ADVERTISERID.V()))) {
				sql.append("     ctype,");
			}
			if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.APPCHL.V())) {
				sql.append("     id name,");
			} else {
				sql.append("     name,");
			}
			if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains("deptId")) {
				sql.append("     IFNULL(osd.name, '') deptName,");
			}
			if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains("investor")) {
				sql.append("     IFNULL(osu.real_name, '') investorName,");
			}
			sql.append("     IFNULL(cost, toDecimal32(0, 3)) cost,");
			sql.append("     IFNULL(rudecost, toDecimal32(0, 3)) rudecost,");
			sql.append("     IFNULL(usrnamenums, 0) usrnamenums,");
			sql.append("     IFNULL(retention2, 0) retention2,");
			sql.append("     IFNULL(retention3, 0) retention3,");
			sql.append("     IFNULL(retention4, 0) retention4,");
			sql.append("     IFNULL(retention5, 0) retention5,");
			sql.append("     IFNULL(retention6, 0) retention6,");
			sql.append("     IFNULL(retention7, 0) retention7,");
			sql.append("     IFNULL(retention8, 0) retention8,");
			sql.append("     IFNULL(paydeviceAll, 0) paydeviceAll,");
			sql.append("     case when worth1 is null then  0  else worth1 end worth1,");
			sql.append("     IFNULL(paydevice1, 0) paydevice1,");
			sql.append("     case when userfeeAll is null then 0  else userfeeAll end userfeeAll,");
			sql.append("     IFNULL(givemoneyAll, toDecimal32(0, 3)) givemoneyAll");
		}
		sql.append(" FROM (");
		//              广告维度
		sql.append("     SELECT ");
		sql.append("		" + groupByColumn).append(Constant.COMMA);
		if (CollectionUtils.isNotEmpty(queryColumn)) {
			sql.append("         ").append(queryColumn.get(0)).append(" as id,");
		}
		sql.append("         cost,");
		sql.append("         rudecost,");
		sql.append("         uuidnums,");
		sql.append("         usrnamenums,");
		sql.append("         paydeviceAll,");
		sql.append("         worth1,");
		sql.append("         paydevice1,");
		sql.append("         userfeeAll,");
		sql.append("         givemoneyAll,");
		sql.append("         retention2,");
		sql.append("         retention3,");
		sql.append("         retention4,");
		sql.append("         retention5,");
		sql.append("         retention6,");
		sql.append("         retention7,");
		sql.append("         retention8");
		sql.append("     FROM (");
		sql.append("         SELECT ");
		sql.append("			 " + groupByColumn).append(Constant.COMMA);
		sql.append("             uuidnums,");
		sql.append("             usrnamenums,");
		sql.append("             paydeviceAll,");
		sql.append("             worth1,");
		sql.append("             paydevice1,");
		sql.append("             userfeeAll,");
		sql.append("             givemoneyAll,");
		sql.append("             retention2,");
		sql.append("             retention3,");
		sql.append("             retention4,");
		sql.append("             retention5,");
		sql.append("             retention6,");
		sql.append("             retention7,");
		sql.append("             retention8");
		sql.append("         FROM (");
		sql.append("             SELECT ");
		sql.append("				" + groupByColumn).append(Constant.COMMA);
		sql.append("                 uuidnums,");
		sql.append("                 usrnamenums,");
		sql.append("                 retention2,");
		sql.append("                 retention3,");
		sql.append("                 retention4,");
		sql.append("                 retention5,");
		sql.append("                 retention6,");
		sql.append("                 retention7,");
		sql.append("                 retention8");
		sql.append("             FROM (");
		//                          新增设备  累计注册账号的设备数");
		sql.append("                 SELECT ");
		sql.append("					" + groupByColumn).append(Constant.COMMA);
		sql.append("                     COUNT(DISTINCT reg.kid) uuidnums,");
		sql.append("                     COUNT(DISTINCT ud.dr_kid) usrnamenums");
		sql.append("                 from (");
		//                              新增设备及其广告归因");
		sql.append("                     SELECT");
		sql.append("                         a.day day,");
		sql.append("                         a.pgid pgid,a.gameid gameid,IFNULL(a.os, 3) os,a.parentchl parentchl,a.chl chl,a.appchl appchl,");
		sql.append("                         wpc.manage investor,"); // 投放人
		sql.append("                         IFNULL(osu.dept_group_id,0) userGroupId,"); // 组别
		sql.append("                         osu.dept_id deptId,"); // 部门
		sql.append("                         vad.status adStatus,"); // 广告状态
		sql.append("                         (case when ad.adid is not null THEN ad.adid else '' end) as adid,");
		sql.append("                         (case when ad.adname is not null THEN ad.adname else '' end) as adidName,");
		sql.append("                         (case when ad.adaccount is not null THEN ad.adaccount else '' end) as advertiserid,");
		sql.append("                         (case when ad.ctype is not null THEN ad.ctype else 0 end) as ctype,");
		sql.append("                         a.uuid uuid,a.kid kid,a.receivetime receivetime,ad.advert_id advert_id,vad.convert_name convertName,vad.deep_convert deepConvert,vad.convert_data_type convertDataType");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                         ,oam.name  adMaterialName");
		}
		sql.append("                     from thirty_game_device_reg a");
		sql.append("                     LEFT JOIN v_thirty_ad_device ad");
		sql.append("                     on a.uuid = ad.uuid");
		sql.append("                     and a.pgid = ad.pgid");
		sql.append("                     and a.day = ad.`day`");
		sql.append("                     LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                     ON wpc.isdelete = 0");
		sql.append("                     AND wpc.parent_code = a.parentchl");
		sql.append("                     AND wpc.chncode = a.chl");
		sql.append("                     LEFT JOIN odsmysql_sys_user osu");
		sql.append("                     ON wpc.manage = osu.user_id");
		sql.append("                     LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                     ON ad.adid = vad.adid");
		sql.append("                     AND ad.ctype = vad.ctype");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                     LEFT JOIN v_odsmysql_creative_new  voc");
			sql.append("                     ON toString(voc.ad_id) = vad.adid ");
			sql.append("                     AND voc.ctype = vad.ctype");
			sql.append("                     LEFT JOIN odsmysql_ad_creative_material oacm");
			sql.append("                     ON oacm.creative_id = voc.creative_id ");
			sql.append("                     LEFT JOIN odsmysql_ad_material_platform oamp");
			sql.append("                     ON oamp.platform_file_id = oacm.platform_file_id ");
			sql.append("                     AND oamp.platform_id = oacm.platform_id ");
			sql.append("                     LEFT JOIN odsmysql_ad_material oam");
			sql.append("                     ON oam.id = oamp.material_id ");
		}
		sql.append("                     where 1=1");
		sql.append("                     and a.day = ").append(day);
		sql.append("                 ) reg");
		sql.append("                 LEFT JOIN (");
//        sql.append("                     -- 新增设备上注册的账号");
		sql.append("                     select");
		sql.append("                         ur.kid ur_kid,");
		sql.append("                         argMax(dr.kid, dr.receivetime) dr_kid,");
		sql.append("                         argMax(ur.usrname, dr.receivetime) usrname");
		sql.append("                     from v_game_account_reg ur");
		sql.append("                     INNER join v_odsmysql_wan_game g");
		sql.append("                     on ur.gameid = g.id");
		sql.append("                     INNER join thirty_game_device_reg dr");
		sql.append("                     on ur.uuid = dr.uuid");
		sql.append("                     and g.pgid = dr.pgid");
		sql.append("                     where 1=1");
		sql.append("                     and  ur.receivetimes >= dr.receivetime");
		sql.append("                     and dr.day = ").append(day);
		sql.append("                     group by ur.kid");
		sql.append("                 ) ud");
		sql.append("                 on reg.kid = ud.dr_kid");
		sql.append("                 where 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(Constant.RIGHT_SMALL_BRANCKET);
		}
//        sql.append("                 -- 筛选条件");
		sql.append(commCond);
		sql.append("                 GROUP BY ");
		sql.append("						").append(groupByColumn);
		sql.append("             ) t001");
		sql.append("             FULL JOIN (");
//        sql.append("                 -- 留存（有登录）");
		sql.append("                 SELECT");
		sql.append("					").append(groupByColumn).append(Constant.COMMA);
		sql.append("                     MAX(niff) dayDiff,");
		sql.append("                     (case when dayDiff < 1 then NULL else IFNULL(uniqExact(activeuuid2), 0) end) retention2,");
		sql.append("                     (case when dayDiff < 2 then NULL else IFNULL(uniqExact(activeuuid3), 0) end) retention3,");
		sql.append("                     (case when dayDiff < 3 then NULL else IFNULL(uniqExact(activeuuid4), 0) end) retention4,");
		sql.append("                     (case when dayDiff < 4 then NULL else IFNULL(uniqExact(activeuuid5), 0) end) retention5,");
		sql.append("                     (case when dayDiff < 5 then NULL else IFNULL(uniqExact(activeuuid6), 0) end) retention6,");
		sql.append("                     (case when dayDiff < 6 then NULL else IFNULL(uniqExact(activeuuid7), 0) end) retention7,");
		sql.append("                     (case when dayDiff < 7 then NULL else IFNULL(uniqExact(activeuuid8), 0) end) retention8");
		sql.append("                 from (");
		sql.append("                     select");
		sql.append("                         argMax(reg.day, reg.receivetime) regDay,");
		sql.append("                         argMax(ul.day, reg.receivetime) activeDay,");
		sql.append("                         regDay AS day,");
		sql.append("                         argMax(reg.pgid, reg.receivetime) pgid,");
		sql.append("                         argMax(reg.gameid, reg.receivetime) AS gameid,");
		sql.append("                         argMax(reg.os, reg.receivetime) AS os,");
		sql.append("                         argMax(reg.parentchl, reg.receivetime) parentchl,");
		sql.append("                         argMax(reg.chl, reg.receivetime) chl,");
		sql.append("                         argMax(reg.appchl, reg.receivetime) appchl,");
		sql.append("                         argMax(wpc.manage, reg.receivetime) investor,"); // 投放人
		sql.append("                         IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId,"); // 组别
		sql.append("                         argMax(osu.dept_id, reg.receivetime) deptId,"); // 部门
		sql.append("                         argMax(ad.adid, reg.receivetime) adidTmp,");
		sql.append("                         argMax(ad.adname, reg.receivetime) adidName,");
		sql.append("                         IFNULL(argMax(ad.adaccount, reg.receivetime),'') advertiserid,");
		sql.append("                         IFNULL(argMax(ad.ctype, reg.receivetime),0) ctype,");
		sql.append("                         (case when adidTmp is not null THEN adidTmp else '' end) as adid,");
		sql.append("                         ul.kid ulkid,");
		sql.append("                         argMax(reg.kid, reg.receivetime) regkid,");
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(regDay)),  today() ) as niff, ");// --距离今天多少天
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(regDay)), parseDateTimeBestEffort(toString(activeDay))) as diff,");
		sql.append("                         argMax(ul.uuid, reg.receivetime) AS uuid,");
		sql.append("                         (case when diff = 1 then regkid else null  end) activeuuid2,");
		sql.append("                         (case when diff = 2 then regkid else null  end) activeuuid3,");
		sql.append("                         (case when diff = 3 then regkid else null  end) activeuuid4,");
		sql.append("                         (case when diff = 4 then regkid else null  end) activeuuid5,");
		sql.append("                         (case when diff = 5 then regkid else null  end) activeuuid6,");
		sql.append("                         (case when diff = 6 then regkid else null  end) activeuuid7,");
		sql.append("                         (case when diff = 7 then regkid else null  end) activeuuid8,");
		sql.append("                         argMax(vad.status, reg.receivetime) adStatus,"); // 广告状态
		sql.append("                         argMax(ad.advert_id, reg.receivetime) advert_id,argMax(vad.convert_name, reg.receivetime) convertName, argMax(vad.deep_convert, reg.receivetime) deepConvert,argMax(vad.convert_data_type, reg.receivetime) convertDataType");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                         ,argMax(oam.name, reg.receivetime)   adMaterialName");
		}
		sql.append("                     from user_login ul");
		sql.append("                     INNER join odsmysql_wan_game g");
		sql.append("                     on ul.gameid = g.id");
		sql.append("                     INNER join thirty_game_device_reg reg");
		sql.append("                     on ul.uuid = reg.uuid");
		sql.append("                     and g.pgid = reg.pgid");
		sql.append("                     LEFT JOIN v_thirty_ad_device ad");
		sql.append("                     on reg.uuid = ad.uuid");
		sql.append("                     and reg.pgid = ad.pgid");
		sql.append("                     and reg.`day` = ad.`day`");
		sql.append("                     LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                     ON wpc.isdelete = 0");
		sql.append("                     AND wpc.parent_code = reg.parentchl");
		sql.append("                     AND wpc.chncode = reg.chl");
		sql.append("                     LEFT JOIN odsmysql_sys_user osu");
		sql.append("                     ON wpc.manage = osu.user_id");
		sql.append("                     LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                     ON ad.adid = vad.adid");
		sql.append("                     AND ad.ctype = vad.ctype");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                     LEFT JOIN v_odsmysql_creative_new  voc");
			sql.append("                      ON toString(voc.ad_id) = vad.adid  ");
			sql.append("                     AND voc.ctype = vad.ctype");
			sql.append("                     LEFT JOIN odsmysql_ad_creative_material oacm");
			sql.append("                     ON oacm.creative_id = voc.creative_id ");
			sql.append("                     LEFT JOIN odsmysql_ad_material_platform oamp");
			sql.append("                     ON oamp.platform_file_id = oacm.platform_file_id ");
			sql.append("                     AND oamp.platform_id = oacm.platform_id ");
			sql.append("                     LEFT JOIN odsmysql_ad_material oam");
			sql.append("                     ON oam.id = oamp.material_id ");
		}
		sql.append("                     where 1=1");
		sql.append("                     and ul.`day` >= ").append(day);
		sql.append("                     and reg.day = ").append(day);
		sql.append("                     and ul.receivetime >= reg.receivetime");
		sql.append("                     GROUP BY ul.kid");
		sql.append("                 ) a");
		sql.append("                 where 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(Constant.RIGHT_SMALL_BRANCKET);
		}
//        sql.append("                 -- 筛选条件");
		sql.append(commCond);
		sql.append("                 GROUP BY ");
		sql.append("						").append(groupByColumn);
		sql.append("             ) t002");
		sql.append("             USING ( ");
		sql.append("				   ").append(groupByColumn);
		sql.append(Constant.RIGHT_SMALL_BRANCKET);
		sql.append("         ) t010");
		sql.append("         FULL JOIN (");
		sql.append("             SELECT");
		sql.append("						").append(groupByColumn).append(Constant.COMMA);
		sql.append("                 paydeviceAll,");
		sql.append("                 worth1,");
		sql.append("                 paydevice1,");
		sql.append("                 userfeeAll,");
		sql.append("                 givemoneyAll");
		sql.append("             FROM ("); // -- 新增设备N日付费
		sql.append("                 select");
		sql.append("						").append(groupByColumn).append(Constant.COMMA);
		sql.append("                     COUNT(DISTINCT regkid) paydeviceAll, ");// -- 新增设备累计付费设备数
		sql.append("                     SUM(worth1) worth1,");
		sql.append("                     COUNT(DISTINCT paykid1) paydevice1,");
		sql.append("                     SUM(sharfee) userfeeAll,");
		sql.append("                     SUM(givemoney) givemoneyAll");
		sql.append("                 from (");
//        sql.append("                  -- 新增设备充值");
		sql.append("                     select");
		sql.append("                         argMax(reg.day, reg.receivetime) regDay,");
		sql.append("                         regDay AS day,");
		sql.append("                         argMax(reg.pgid, reg.receivetime) pgid,");
		sql.append("                         argMax(reg.gameid, reg.receivetime) AS gameid,");
		sql.append("                         IFNULL(argMax(reg.os, reg.receivetime), 3) AS os,");
		sql.append("                         argMax(reg.parentchl, reg.receivetime) parentchl,");
		sql.append("                         argMax(reg.chl, reg.receivetime) chl,");
		sql.append("                         argMax(reg.appchl, reg.receivetime) appchl,");
		sql.append("                         argMax(wpc.manage, reg.receivetime) investor,"); // 投放人
		sql.append("                         IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId,"); // 组别
		sql.append("                         argMax(osu.dept_id, reg.receivetime) deptId,"); // 部门
		sql.append("                         argMax(vad.status, reg.receivetime) adStatus,"); // 广告状态
		sql.append("                         argMax(ad.adid, reg.receivetime) adidTmp,");
		sql.append("                         argMax(ad.adname, reg.receivetime) adidName,");
		sql.append("                         IFNULL(argMax(ad.adaccount, reg.receivetime),'') advertiserid,");
		sql.append("                         IFNULL(argMax(ad.ctype, reg.receivetime),0) ctype,");
		sql.append("                         (case when adidTmp is not null THEN adidTmp else '' end) as adid,");
		sql.append("                         rc.kid rckid,");
		sql.append("                         argMax(reg.kid, reg.receivetime) regkid,");
		sql.append("                         argMax(rc.day, reg.receivetime) payday,");
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(day)),  today() ) as  cur_diff, ");// --距离今天多少天
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(day)), parseDateTimeBestEffort(toString(payday))) as pay_diff,");
		sql.append("                         argMax(rc.uuid, reg.receivetime) AS payuuid,");
		sql.append("                         argMax(rc.fee, reg.receivetime) fee,");
		sql.append("                         argMax(rc.givemoney, reg.receivetime) givemoney,");
		sql.append("                         argMax(rc.sharfee, reg.receivetime) sharfee,");
		sql.append("                         (case when pay_diff = 0 then sharfee else 0  end) worth1,");
		sql.append("                         (case when pay_diff = 0 then regkid else null  end) paykid1,");
		sql.append("                         argMax(vad.convert_name, reg.receivetime)  convertName,argMax(vad.deep_convert, reg.receivetime) deepConvert,argMax(vad.convert_data_type, reg.receivetime) convertDataType,");
		sql.append("                         argMax(rc.createtime, reg.receivetime) createtime");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                         ,argMax(oam.name, reg.receivetime)   adMaterialName");
		}
		sql.append("                     from v_original_user_recharge_share rc");
//		sql.append("                     left join v_odsmysql_wan_game g");
//		sql.append("                     on rc.gameid=g.id");
		sql.append("                     LEFT join thirty_game_device_reg reg");
		sql.append("                     on rc.uuid=reg.uuid");
		sql.append("                     and rc.pgid = reg.pgid");
		sql.append("                     LEFT JOIN v_thirty_ad_device ad");
		sql.append("                     on reg.uuid = ad.uuid");
		sql.append("                     and reg.day = ad.day");
		sql.append("                     and reg.pgid = ad.pgid");
		sql.append("                     LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                     ON wpc.isdelete = 0");
		sql.append("                     AND wpc.parent_code = reg.parentchl");
		sql.append("                     AND wpc.chncode = reg.chl");
		sql.append("                     LEFT JOIN odsmysql_sys_user osu");
		sql.append("                     ON wpc.manage = osu.user_id");
		sql.append("                     LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                     ON ad.adid = vad.adid");
		sql.append("                     AND ad.ctype = vad.ctype");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                     LEFT JOIN v_odsmysql_creative_new voc");
			sql.append("                      ON toString(voc.ad_id) = vad.adid  ");
			sql.append("                     AND voc.ctype = vad.ctype");
			sql.append("                     LEFT JOIN odsmysql_ad_creative_material oacm");
			sql.append("                     ON oacm.creative_id = voc.creative_id ");
			sql.append("                     LEFT JOIN odsmysql_ad_material_platform oamp");
			sql.append("                     ON oamp.platform_file_id = oacm.platform_file_id ");
			sql.append("                     AND oamp.platform_id = oacm.platform_id ");
			sql.append("                     LEFT JOIN odsmysql_ad_material oam");
			sql.append("                     ON oam.id = oamp.material_id ");
		}
		sql.append("                     where 1=1 and");
		sql.append("                     reg.`day` = ").append(day);
		sql.append("                     AND rc.`day` >= reg.`day`");
		sql.append("                     group by rc.kid");
		sql.append("                 ) a");
		sql.append("                 where 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(Constant.RIGHT_SMALL_BRANCKET);
		}
//        sql.append("                 -- 筛选条件");
		sql.append(commCond);
		sql.append("                 GROUP BY ");
		sql.append("						").append(groupByColumn);
		sql.append("             ) t003");
		sql.append("         ) t020");
		sql.append("         USING ( ");
		sql.append("						").append(groupByColumn);
		sql.append(Constant.RIGHT_SMALL_BRANCKET);
		sql.append("     ) t100");
		sql.append("     	FULL JOIN (");
//        sql.append("             -- 计划广告数据");
		sql.append("             SELECT ");
		sql.append("						").append(groupByColumn).append(Constant.COMMA);
		sql.append("                 SUM(cost) cost,");
		sql.append("                 SUM(rudecost) rudecost");
		sql.append("             from (");
		sql.append("                 SELECT");
		sql.append("                     b.date day,");
		sql.append("                     IFNULL(g.pgid, 0) pgid,");
		sql.append("                     IFNULL(ap.gameid, 0) gameid,");
		sql.append("                     IFNULL(g.os, 3) os,");
		sql.append("                     (case when ap.adid is not null and ap.adid !='' then ap.parentchl else '-' end) parentchl,");
		sql.append("                     (case when ap.adid is not null and ap.adid !='' then ap.chl else '-' end) chl,");
		sql.append("                     (case when ap.adid is not null and ap.adid !='' then ap.appchl else '-' end) appchl,");
		sql.append("                     osu.user_id investor,"); // 投放人
		sql.append("                     IFNULL(osu.dept_group_id,0) userGroupId,"); // 组别
		sql.append("                     osu.dept_id deptId,"); // 部门
		sql.append("                     vad.status adStatus,"); // 广告状态
		sql.append("                     b.ad_id adid,");
		sql.append("                     b.ad_name adidName,");
		sql.append("                     IFNULL(b.ad_account,'') advertiserid,"); // 广告账户
		sql.append("                     IFNULL(b.ctype,0) ctype,");
		sql.append("                     b.adconvert adconvert,");
		sql.append("                     b.rudecost rudecost,");
		sql.append("                     b.cost cost,vad.convert_name convertName,vad.deep_convert deepConvert,vad.convert_data_type convertDataType ");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                     ,oam.name  adMaterialName");
		}
		sql.append("                 from  v_adid_rebate_day b");
		sql.append("                 left JOIN ad_ptype ap");
		sql.append("                 on ap.adid = b.ad_id");
		sql.append("                 left JOIN v_odsmysql_wan_game g");
		sql.append("                 on ap.gameid=g.id");
		sql.append("                 LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                 ON wpc.isdelete = 0");
		sql.append("                 AND wpc.parent_code = ap.parentchl");
		sql.append("                 AND wpc.chncode = ap.chl");
		sql.append("                 LEFT JOIN odsmysql_ad_account aa");
		sql.append("                 ON aa.advertiser_id = b.ad_account");
		sql.append("                 AND aa.media_code = toString(b.ctype)");  // 类型不一致
		sql.append("                 LEFT JOIN odsmysql_sys_user osu");
		sql.append("                 ON aa.throw_user = toString(osu.user_id)");
		sql.append("                 LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                 ON b.ad_id = vad.adid");
		sql.append("                 AND b.ctype = vad.ctype");
		if (StringUtils.isNotBlank(adMaterialName)) {
			sql.append("                 LEFT JOIN v_odsmysql_creative_new voc");
			sql.append("                      ON toString(voc.ad_id) = vad.adid  ");
			sql.append("                 AND voc.ctype = vad.ctype");
			sql.append("                 LEFT JOIN odsmysql_ad_creative_material oacm");
			sql.append("                 ON oacm.creative_id = voc.creative_id ");
			sql.append("                 LEFT JOIN odsmysql_ad_material_platform oamp");
			sql.append("                 ON oamp.platform_file_id = oacm.platform_file_id ");
			sql.append("                 AND oamp.platform_id = oacm.platform_id ");
			sql.append("                 LEFT JOIN odsmysql_ad_material oam");
			sql.append("                 ON oam.id = oamp.material_id ");
		}
		sql.append("                 WHERE 1=1");
		sql.append("                 and b.date = ").append(day);
		if (isSys == null || isSys != 1) {
			sql.append("                 AND b.ad_account IN (").append(adAccounts).append(Constant.RIGHT_SMALL_BRANCKET);
		}
		sql.append("             ) a");
		sql.append("             where 1=1");
		sql.append(commCond);
		sql.append("             GROUP BY ");
		sql.append("				").append(groupByColumn);
		sql.append("     ) t030");
		sql.append("     USING ( ");
		sql.append("				").append(groupByColumn);
		sql.append(Constant.RIGHT_SMALL_BRANCKET);
		sql.append(" ) t1000");
		if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.ADID.V())) {
			sql.append("     LEFT JOIN (");
			sql.append("         SELECT");
			sql.append("             t050.adid adidTmp,");
			sql.append("             t050.name name,");
			sql.append("             t051.ad_account adAccountId,");
			sql.append("             t051.name adAccountName,");
			sql.append("             t050.ctype pctype,");
			sql.append("             t050.convert_name convertName,");
			sql.append("             t050.convert_descri convertDescri,");
			sql.append("             t050.create_time createTime");
			sql.append("         FROM v_odsmysql_adid_new t050");
			sql.append("         LEFT JOIN v_odsmysql_advertiser t051 ON");
			sql.append("         t050.advertiser_id = t051.ad_account");
			sql.append("         AND t050.ctype = t051.ctype");
			sql.append("     ) t2000");
			sql.append("     ON t1000.id = t2000.adidTmp AND t1000.ctype = t2000.pctype ");
		}
		if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.PGID.V())) {
			sql.append("     LEFT JOIN (");
			sql.append("     SELECT");
			sql.append("     t050.id pgId,");
			sql.append("     t050.gname name");
			sql.append("     FROM odsmysql_parent_game t050");
			sql.append("     ) t2000");
			sql.append("	ON t1000.id = t2000.pgId");
		}
		if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.GAMEID.V())) {
			sql.append("     LEFT JOIN (");
			sql.append("     SELECT");
			sql.append("     t050.id gameId,");
			sql.append("     t050.gname name");
			sql.append("     FROM odsmysql_wan_game t050");
			sql.append("     ) t2000");
			sql.append("	ON t1000.id = t2000.gameId");
		}
		if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.PARENTCHL.V())) {
			sql.append("     LEFT JOIN (");
			sql.append("     SELECT");
			sql.append("     t050.chncode chnCode,");
			sql.append("     t050.chnname name");
			sql.append("     FROM odsmysql_wan_promotion_channel_v3 t050");
			sql.append("     ) t2000");
			sql.append("	ON t1000.id = t2000.chnCode");
		}
		if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.ADVERTISERID.V())) {
			sql.append("     LEFT JOIN (");
			sql.append("     SELECT");
			sql.append("     t050.advertiser_id advertiser_id,");
			sql.append("     t050.advertiser_name name,");
			sql.append("     t050.media_code mediaCode");
			sql.append("     FROM odsmysql_ad_account t050");
			sql.append("     ) t2000");
			sql.append("	ON t1000.id = t2000.advertiser_id and t2000.mediaCode = toString(t1000.ctype)");
		}
		sql.append("     WHERE 1=1 ");
		// 处理汇总 计划属性报表汇总
		if (searchVo.getCycleType() != 4) {
			sql.append(" ORDER BY day DESC");
			if (CollectionUtils.isNotEmpty(queryColumn) && queryColumn.contains(PlanAttrStatTypeEnum.ADID.V())) {
				sql.append(" , createTime DESC");
			}
		}
		log.debug("sql : [{}]", sql.toString());
		List<PlanBaseAttrVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(PlanBaseAttrVo.class));
		return list;
	}

	private String convertGroupByColumns(List<String> queryColumn) {
		StringBuffer sb = new StringBuffer("day");
		if (CollectionUtil.isEmpty(queryColumn)) {
			return sb.toString();
		}
		for (String column : queryColumn) {
			sb.append(Constant.COMMA);
			sb.append(column);
		}
		if (queryColumn.contains(PlanAttrStatTypeEnum.ADID.V()) || queryColumn.contains(PlanAttrStatTypeEnum.ADVERTISERID.V())) {
			sb.append(" ,ctype");
		}
		return sb.toString();
	}
}
